﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;
using System.Configuration;


namespace HRAssistant.Data
{
   public class ExecuteSP
    {
       public static DataSet OperateSP(string sp_name, bool isAll, DateTimePicker start, DateTimePicker end, TextBox eid) 
       //public static DataSet OperateSP(string sp_name)
       {
           MySqlConnection mysqlcon;
           MySqlDataAdapter mysqldata = new MySqlDataAdapter();
           DataSet dataset = new DataSet();
           mysqlcon = new MySqlConnection("server=localhost; user id=root; password=root; database=orangehrm_mysql; pooling=false;");
           mysqldata.SelectCommand = new MySqlCommand();
           mysqldata.SelectCommand.Connection = mysqlcon;
           mysqldata.SelectCommand.CommandText = sp_name;
           mysqldata.SelectCommand.CommandType = CommandType.StoredProcedure;

           //设置参数，添加到数据库  
           MySqlParameter startdate_parameter = new MySqlParameter("?startdate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
           startdate_parameter.Value = start.Value;
           mysqldata.SelectCommand.Parameters.Add(startdate_parameter);
           //+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
           MySqlParameter enddate_parameter = new MySqlParameter("?enddate", MySqlDbType.DateTime);//mysql的存储过程参数是以?打头的！！！！  
           enddate_parameter.Value = end.Value;
           mysqldata.SelectCommand.Parameters.Add(enddate_parameter);
           //++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++  
           if (!isAll)
           {
               MySqlParameter eid_parameter = new MySqlParameter("?eid", MySqlDbType.Int16);//mysql的存储过程参数是以?打头的！！！！  
               eid_parameter.Value = Convert.ToInt32(eid.Text.Trim());
               mysqldata.SelectCommand.Parameters.Add(eid_parameter);
           }

           try
           {
               mysqldata.Fill(dataset, sp_name);
               MessageBox.Show("结果集个数为："+dataset.Tables.Count);
               return dataset;

           }
           catch (MySqlException ex)
           {
               MessageBox.Show(ex.Message);
               return null;
           }
           finally
           {
               mysqlcon.Close();
           }

       }
    }
}
